var express = require("express");
var uunewid = require("uuid");
var router = express.Router();
const { ohana } = require("ohana-node-orm");
const tableName = "SAP_JIANGDU_TALENTS";
let log = require("cf-nodejs-logging-support");
log.setLoggingLevel("info");
var moment = require("moment");

const { checkProperty, checkAuth } = require("../utils/utils");
var constants = require("../utils/constants");

router.get("/getTalentStatistics", function (req, res, next) {
  const user = checkAuth(req.user, constants.BASICAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
  }
  getTalentStatistics().then((result) => {
    res.send(result);
  });
});
async function getTalentStatistics() {
  let ohana_instance = new ohana(tableName);
  let result = await ohana_instance.raw(
    "select TYPE, count(*) as TOTAL from " +
      tableName +
      " where TYPE is not NULL group by TYPE order by TOTAL desc  limit 5"
  );
  var resultData = {};
  resultData.topType = result;
  //monthly growth
  var dateQuery =
    "select count(*) as TOTAL from " +
    tableName +
    " where CREATED_AT between '" +
    moment().days(-30).format("YYYY-MM") +
    "' and '" +
    moment().format("YYYY-MM") +
    "'";
  console.log(dateQuery);
  var monthlygrowth = await ohana_instance.raw(dateQuery);
  resultData.monthlygrowth = monthlygrowth[0].TOTAL;
  return resultData;
}

router.get("/count", async (req, res, next) => {
  // #swagger.tags = ['人才']
  // #swagger.summary = '计数'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  let { USER_ID } = req.query;
  getCount(USER_ID)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getCount(USER_ID) {
  let result = null;
  let ohana_instance = new ohana(tableName);
  if (USER_ID) {
    result = await ohana_instance.raw(
      "select count(*) as total from " +
        tableName +
        " where USER_ID_USER_ID = '" +
        USER_ID +
        "'"
    );
  } else {
    result = await ohana_instance.raw(
      "select count(*) as total from " + tableName
    );
  }
  return result[0];
}

router.post("/", function (req, res, next) {
  // #swagger.tags = ['人才']
  // #swagger.summary = '获取talent'
  // #swagger.description = '获取人才列表or搜索'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/talent_get"
                      }  
                  },
              }
      } */
  const userNew = checkAuth(req.user, constants.BASICAUTH);
  let { data } = req.body;
  if (data == undefined) {
    data = {};
  }
  data.user = userNew;
  data.type = req.body.type ? req.body.type : "view";
  getTalent(data)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

/**update Talent  */
router.put("/", function (req, res, next) {
  // #swagger.tags = ['人才']
  // #swagger.summary = '修改talent'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/talent_update"
                      }  
                  },
              }
      } */
  const { data } = req.body;
  data.UPDATED_AT = moment().format("YYYY-MM-DD");
  updateTalent(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/tags", function (req, res, next) {
  // #swagger.tags = ['人才']
  // #swagger.summary = '获取某个talent下全部tag'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/talents_tags"
                      }  
                  },
              }
      } */

  const { data } = req.body;
  getTalentTags(data)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/addTags", function (req, res, next) {
  // #swagger.tags = ['人才']
  // #swagger.summary = '给某个talent添加tag'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/talent_addTags"
                      }  
                  },
              }
      } */
  const { data } = req.body;
  insertTalentData(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else if (result == -1) {
        res.status(200).send({ code: -1, message: "已有，请勿重复点击" });
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/deleteTags", function (req, res, next) {
  // #swagger.tags = ['人才']
  // #swagger.summary = '给某个talent删除tag'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/talent_deleteTags"
                      }  
                  },
              }
      } */
  const { data } = req.body;
  deletaTalentTag(data)
    .then((result) => {
      if (result >= 1) {
        res.sendStatus(200);
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

/** insert Talent */
router.post("/add", function (req, res, next) {
  // #swagger.tags = ['人才']
  // #swagger.summary = '添加talent'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/user_register"
                      }  
                  },
              }
      } */
  const { data } = req.body;
  const user = checkAuth(req.user, constants.BASICAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
    return;
  }

  data.CREATED_AT=moment().format("YYYY-MM-DD");
  data.UPDATED_AT=moment().format("YYYY-MM-DD");
  data.USER_ID_USER_ID = user.user_id;
  data.USER_NAME = user.user_name;
  data.TALENT_ID = uunewid.v4();
  data.STATUS = constants.STATUSOFF;
  if (!user.admin) {
    data.COMPANY_NAME = user.company ? user.company : "empty";
  }

  insertData(data)
    .then((result) => {
      if (result == 1) {
        res.status(200).send({ TALENT_ID: data.TALENT_ID });
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

/**delete Talent  */
router.delete("/", function (req, res, next) {
  // #swagger.tags = ['人才']
  // #swagger.summary = '删除talent'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/talent_delete"
                      }  
                  },
              }
      } */
  const { data } = req.body;
  deleteTalent(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});
async function getTalent(condition) {
  let result = [];
  const t_Talent = new ohana(tableName);
  var sqlText = generateSQLText(condition);
  result = await t_Talent.raw(sqlText);
  if (condition.TALENT_ID) {
    if (result.length > 0) {
      if (
        result[0].COMPANY_NAME == condition.user.company ||
        result[0].STATUS == constants.STATUSON
      ) {
        return result;
      } else {
        return [];
      }
    }
  }
  return result;
}
function generateSQLText(condition) {
  var sqlText = 'SELECT * FROM "SAP_JIANGDU_TALENTS"';
  //管理员 返回全部
  if (condition.user.admin) {
    if (condition.NAME) {
      sqlText +=
        " where TALENT_NAME LIKE '%" +
        condition.NAME +
        "%' or WORK_ACHIEVEMENT like'%" +
        condition.NAME +
        "%' or SPECIAL_TECH like '%" +
        condition.NAME +
        "%'";
    }
    // console.log(sqlText);
    return sqlText;
  }
  //非管理员
  if (!condition || !condition.NAME) {
    //单个
    if (condition.TALENT_ID) {
      sqlText =
        "SELECT * from SAP_JIANGDU_TALENTS where TALENT_ID = '" +
        condition.TALENT_ID +
        "'";
      return sqlText;
    }
  } else {
    //搜索
    sqlText =
      'SELECT  * FROM "SAP_JIANGDU_TALENTS" where ("TALENT_NAME" LIKE \'%' +
      condition.NAME +
      "%' or WORK_ACHIEVEMENT like'%" +
      condition.NAME +
      "%' or SPECIAL_TECH like '%" +
      condition.NAME +
      "%')";
  }
  if (condition.type == constants.BACKEND && condition.user.admin == false) {
    if (condition.user.level == constants.LEVELTWO) {
      sqlText += " where USER_ID_USER_ID = '" + condition.user.user_id + "'";
    } else {
      sqlText += " where COMPANY_NAME = '" + condition.user.company + "'";
    }
  } else {
    if (condition.NAME) {
      sqlText += " and STATUS = '" + constants.STATUSON + "'";
    } else {
      sqlText += " where STATUS = '" + constants.STATUSON + "'";
    }
  }
  if (condition.page) {
    const { page } = condition;
    const pageOffset = page.number * page.page;
    var pageText = " limit " + page.number + " offset " + pageOffset;
    sqlText += pageText;
  }
  return sqlText;
}

async function insertData(body) {
  const t_Talent = new ohana(tableName); // new ohana('table_name');
  body.STATUS = constants.STATUSOFF;
  try {
    // console.log(body)
    Object.keys(body).forEach((item) => {
      if(item!="DEGREE"&&item!='CAREER_YEAR'&&item!="SEX"){
        if (!body[item] || body[item] == "") delete body[item];
      }
    });
    const result = await t_Talent.insert(body);
    return result;
  } catch (err) {
    log.error(err);
  }
}

async function updateTalent(body) {
  const t_Talent = new ohana(tableName);
  const result = await t_Talent.update(
    {
      TALENT_ID: body.TALENT_ID,
    },
    body
  );
  return result;
}

async function deleteTalent(body) {
  const t_Talent = new ohana(tableName); // new ohana('table_name');
  try {
    const result = await t_Talent.raw(
      "delete from " + tableName + " where TALENT_ID = '" + body.TALENT_ID + "'"
    );
    // delete recommend //delete
    t_Talent.raw(
      "delete from " +
        constants.RECOMMENDLISTTABLE +
        " where RECOMMENDED_ID ='" +
        body.TALENT_ID +
        " '"
    );
    t_Talent.raw(
      "delete from " +
        constants.FAVORITETABLE +
        " where FAVOURITE_ID ='" +
        body.TALENT_ID +
        " '"
    );
    return result;
  } catch (error) {
    log.error(error);
    return -1;
  }
}

async function get_Talent_tag(tag_id) {
  const t_Talent_tags = new ohana("SAP_JIANGDU_TAGS");
  const tag = await t_Talent_tags.findOne({
    TAG_ID: tag_id,
  });
  return tag;
}

async function getTalentTags(body) {
  const t_Talent = new ohana("SAP_JIANGDU_TAG_TALENTS");
  //fisrt get tag ids attached with target Talent by Talent id
  const tag_ids = await t_Talent.find({
    TALENT_ID_TALENT_ID: body.TALENT_ID_TALENT_ID,
  });
  let tags = [];
  for (var i = 0; i < tag_ids.length; i++) {
    const tag = await get_Talent_tag(tag_ids[i].TAG_ID_TAG_ID);
    tags.push(tag[0]);
  }
  return tags;
}

async function insertTalentData(data) {
  const t_TalentTags = new ohana("SAP_JIANGDU_TAG_TALENTS"); // new ohana('table_name');
  const resultNumb = await t_TalentTags.find(data);
  if (resultNumb.length > 0) {
    return -1;
  }
  const result = await t_TalentTags.insert(data);
  return result;
}

async function deletaTalentTag(data) {
  const t_TalentTags = new ohana("SAP_JIANGDU_TAG_TALENTS"); // new ohana('table_name');
  const result = await t_TalentTags.raw(
    'delete from "SAP_JIANGDU_TAG_TALENTS" where TALENT_ID_TALENT_ID = \'' +
      data.TALENT_ID_TALENT_ID +
      "' and TAG_ID_TAG_ID = '" +
      data.TAG_ID_TAG_ID +
      "'"
  );
  return result;
}

module.exports = router;
